<?php 
$req_path="./";
include_once($req_path."include/ini.php");
require_once($req_path."include/db.php");

class M_return extends DB{
	private $dbConn;
	private $rowCount="0";
	private $ID="0";
	private $sqlSelect =" select  * from tb_return_product r  Where 1=1 and r.IsDeleted = 0 " ;
	private $totalpage="0";
	
	function m_return(){
		parent::__construct();
	}
	function getListPage(){
		global $list_page;
		return $list_page;
	}
	function getRunning($type='return'){
		$sql ="select value from tb_running where name='".$type."' and r_date=current_date() " ;
		$result = $this->ExecuteData($sql);
		$cnt=$result->RecordCount();
		
		if ( $cnt > 0 ) {
			$row=$result->FetchRow();
			$sqlupdate="update tb_running set value=value+1 where name='".$type."'";
			$this->ExecuteData($sqlupdate);
			return $row["value"]+1;	
		}else{
			$sqlupdate="update tb_running set value=1 , r_date=current_date() where name='".$type."'";
			$this->ExecuteData($sqlupdate);
			return 1;
		}
	}
	function getData( $code=null ,$branch=0, $returndatefrom=null, $returndateto=null, $status=2 ,$pageStart=1, $IsHo=0){
		global $list_page;
		$sqlscript= "select r.ID , r.Code, r.Branch as BranchID , b.BranchName, coalesce(DATE_FORMAT( ReturnDate, '%d/%m/%Y' ),'') ReturnDate, "
				." Case when r.Status=1 then 'Created' when r.Status=2 then 'Return' else 'Closed' end Status ,"
				." Amount ,Remark ,ReturnBy , r.Status  as StatusID"
				." from tb_return_product r "
				." left join tb_branch b on r.Branch=b.ID "
				." Where 1=1 and r.IsDeleted = 0 ";
		
		if (isset($code) && $code!=""){
			$sqlscript.= " and r.Code = '". $code."'";
		}
		if (isset($branch) && $branch!="0"){
			$sqlscript.= " and r.Branch = ". $branch;
		}
		if (isset($returndatefrom) && $returndatefrom!=""){
			list($day, $month, $year) = split('/', $returndatefrom);
			$Rfrom= $year."/".$month."/".$day;
			list($day, $month, $year) = split('/', $returndateto);
			$Rto= $year."/".$month."/".$day;
			$sqlscript.= " and r.ReturnDate Between '". $Rfrom."' and '".$Rto."'";
		}
		if (isset($status) && $status!=0){
			$sqlscript.= " and r.Status = ". $status;
		}
		
		if ($IsHo==1){
			/// When branch create return product don't show
			$sqlscript.= " and r.Status  <> 1 ";
		}
		
		//Order 
		$sqlscript.= " order by r.ReturnDate desc ,r.Status Asc " ;
		//Execute
		$result = $this->ExecuteData($sqlscript);
		//Get all rows
		$rows =$result->RecordCount();
	
		//Page limit
		$sqlscript.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		$result = $this->ExecuteData($sqlscript);		
	
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;
	}
	function checkRemainProduct($id, $bid){
		$sql = "  SELECT MainAmt
				 FROM tb_stock_product t
				 where t.ProductID =".$id." and t.BranchID =".$bid;

		 $result = $this->ExecuteData($sql);
		 $row=$result->FetchRow();
		return $row['MainAmt'];
	}
	
	function getProductDetail($id=0, $amt=0,$type=1){
		$sqlscript= " Select p.ID, ProductName , u.Unit, Price ,".$amt ." as Amount,t.Type ";
		$sqlscript.= " from tb_product p ";
		$sqlscript.= " join tb_units u on p.MainUnit=u.ID  ";
		$sqlscript.= " join tb_return_type t on t.ID=$type "
				. " where IsDeleted=0 ";
		$sqlscript.= " and p.ID='". $id."'";
		$result = $this->ExecuteData($sqlscript);
		return $result->FetchRow();
	}
	//for button click
	// function getHeaderData($id){
		// $sqlscript= "select r.ID , r.Code, r.Branch "
				// ." from tb_Return_product r "
				// ." Where 1=1 "
				// ." and r.ID=".$id ;
				
		// $result = $this->ExecuteData($sqlscript);	
				
		// return $result->FetchRow();
	// }
	
	function getViewDataDetail($id){
			$sql = 'SELECT re.ID, p.ID AS ProductID, p.ProductName, pt.Type as ProductType, c.CatName, u.Unit'
        . ' , re.Amounts_s as Amount, t.Type  , re.Amounts_r, t2.Type as ReturnType_r'
        . ' FROM tb_return_product_detail re'
        . ' LEFT JOIN tb_product p ON re.Product = p.ID'
        . ' LEFT JOIN tb_product_type pt ON p.Type = pt.ID'
        . ' LEFT JOIN tb_product_category c on c.CatID=p.Category'
        . ' LEFT Join tb_units u on u.ID=p.MainUnit'
        . ' LEFT JOIN tb_return_type t ON t.ID = re.ReturnType_s'
		. ' LEFT JOIN tb_return_type t2 on t2.ID= re.ReturnType_r'
        . ' WHERE 1=1'
        . ' AND re.ReturnID ='.$id
        . ' Order by p.ProductName  ';
		$result2 = $this->ExecuteData($sql);
		return $result2 ;
	}
	////////////////////////////////////////
	
	//for view data
	function ViewHeader($id){
		$sqlscript= "select r.ID , r.Code, r.Branch , b.BranchName " 
				." , coalesce(ReturnBy,'-') as ReturnBy "
				." , coalesce(r.CreatedBy,'-') as CreatedBy "
				." , coalesce(r.UpdatedBy,'-') as UpdatedBy "				
				." , coalesce(DATE_FORMAT( r.ReturnDate, '%d/%m/%Y' ),'-') as ReturnDate "
				." , coalesce(DATE_FORMAT( r.CreatedDate, '%d/%m/%Y' ),'-') as CreatedDate  "
				." , coalesce(DATE_FORMAT( r.UpdatedDate, '%d/%m/%Y' ),'-') as UpdatedDate  "
				." , Case when r.Status=1 then 'Created' When r.Status=2 then 'Return' else 'Closed' end Status"
				." , r.Status as iStatus , Remark , r.Amount , r.Status  as StatusID"
				." from tb_return_product r "
				." left join tb_branch b on r.Branch=b.ID Where 1=1"
				." and r.ID=".$id
				." and r.IsDeleted = 0 ";
				
		$result = $this->ExecuteData($sqlscript);		
		return $result->FetchRow();
	}
	// View  detail
	function ViewDetail($id,$pageStart=1,$mode='d'){
		global $list_page;
		 
		$sql = 'SELECT re.ID, p.ID AS ProductID, p.ProductName, pt.Type as ProductType, c.CatName, u.Unit'
        . ' , re.Amounts_s, t.Type as ReturnType_s, t.ID  as typeid_s , re.Amounts_r, t2.Type as ReturnType_r , t2.ID as typeid_r '
        . ' FROM tb_return_product_detail re'
        . ' LEFT JOIN tb_product p ON re.Product = p.ID'
        . ' LEFT JOIN tb_product_type pt ON p.Type = pt.ID'
        . ' LEFT JOIN tb_product_category c on c.CatID=p.Category'
        . ' LEFT Join tb_units u on u.ID=p.MainUnit'
        . ' LEFT JOIN tb_return_type t ON t.ID = re.ReturnType_s'
		. ' LEFT JOIN tb_return_type t2 on t2.ID= re.ReturnType_r'
        . ' WHERE 1=1'
        . ' AND re.ReturnID ='.$id
        . ' Order by p.ProductName  ';

		//Execute
		$result = $this->ExecuteData($sql);
		//Get all rows
		$rows =$result->RecordCount();		
		//Page limit
		//$sql.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		//return $sql;
		// 'd' => detail mode 
		if ($mode=='d'){
			$result = $this->ExecuteData($sql);		
		}
	
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;		
	}
	//End  view data
	
	//update amount after confirm product
	function UpdateReturnProduct($id, $amt=0,$type=1){
		$sql=" Update tb_return_product_detail ";
		$sql.=" Set ReturnType_r =".$type;
		$sql.=" , Amounts_r =".$amt;
		$sql.=" Where ID=".$id; 
		
		$this->ExecuteData($sql);
		//echo $sql;
	} 
	
	function UpdateStatustoClose($id,$user){
		$sqlupdate="Update tb_return_product "
					." set Status=3 "
					.", UpdatedDate=Now() "
					.", UpdatedBy='".$user."'"
					." Where ID=".$id;					 
		$this->ExecuteData($sqlupdate);
		//$m->Excute($sqlupdate);
	}
	
	function Excute($sql){
		$this->ExecuteData($sql);
	}
	function getRowCount(){
		return $this->rowCount;
	}
	
	function getLastInsert(){
		return $this->ID;
	}
	
	function InsertData($val){
		if (isset($val)){
			$sqlInsert=$this->InsertSqlScript($this->sqlSelect, $val);
			$this->ExecuteData($sqlInsert);
			$this->ID = $this->getLastID();
			return $this->ID;
		}
	}
	function updateData($sql, $val){
		if(isset($val)){
			$sqlUpdate=$this->UpdateSqlScript($sql,$val);
			return $this->ExecuteData($sqlUpdate);
		}
	}
	function delData($id , $CreateUser){
		if(isset($id)){
			$sqlDelete = "update  tb_return_product  set IsDeleted = 1 , UpdatedDate = NOW(), UpdatedBy='".$CreateUser."' where ID='".$id."'";
			$this->ExecuteData($sqlDelete);
			//delete detail
			//$this->delDataDetail($id);
			return "c";
		}
	}
	function delDataDetail($id){
			$sqlDelete = "delete from tb_return_product_detail where ReturnID='".$id."'";
			$this->ExecuteData($sqlDelete);
	}
	function getTotalPage(){
		return $this->totalpage;
	}
	function getProductList($bid){
		$sql = "select distinct p.ID as ID, p.ProductName as Name from tb_stock_product s join tb_product p on p.ID=s.ProductID "
			." where s.BranchID='".$bid."' and s.Amount>0 and p.IsDeleted=0 and p.Type=1 order by ProductName asc ";
 	     
        $result = $this->ExecuteData($sql);
		
		 while($row = $result->FetchRow()){
			$ar[$row["Name"]] = $row["ID"];
		 }

		return $ar;
	}
	function getDataCBO($t="b"){
		if ($t=="b"){
			$sqlscript= "select ID as ID, BranchName as Name from tb_branch where IsActived=1  order by  BranchName ";
		}	
		else if ($t=="p"){
			$sqlscript= "select ID as ID, ProductName as Name from tb_product where 1=1 and IsDeleted=0 and Type=1 ";
		}
				
		$result = $this->ExecuteData($sqlscript);
		
		 while($row = $result->FetchRow()){
				$ar[$row["Name"]] = $row["ID"];
		 }
		 
		return $ar;
	}
}
?>